package com.imooc.dao;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.ibatis.session.SqlSession;

import com.imooc.bean.Message;
import com.imooc.db.DBAccess;

/**
* Created By lijiliang on 2018年7月24日 上午11:11:21
* 和message相关的数据库操作
*/

public class MessageDao {
	/**
	 * 根据查询条件获取数据
	 * @param command
	 * @param description
	 * @return
	 */
	
	public List<Message> queryMessageList(String command,String description) {
		DBAccess dbAccess=new DBAccess();
		List<Message> messageList = null;
		Message message=new Message();
		message.setCommand(command);
		message.setDescription(description);
		SqlSession sqlSession=null;
		try {
			sqlSession=dbAccess.getSqlSession();
			//通过sqlSession执行sql语句
			messageList=sqlSession.selectList("Message.queryMessageList",message);
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			if(sqlSession!=null) {
				sqlSession.close();				
			}

		}
		
		return messageList;
	}
	
	/**
	 * 单条删除
	 * @param id
	 */
	public void deleteOne(int id) {
		DBAccess dbAccess=new DBAccess();
		
		SqlSession sqlSession=null;
		try {
			sqlSession=dbAccess.getSqlSession();
			//通过sqlSession执行sql语句
			sqlSession.delete("Message.deleteOne",id);
			sqlSession.commit();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			if(sqlSession!=null) {
				sqlSession.close();				
			}

		}
	}
	
	public static void main(String[] args) {
		MessageDao messageDao=new MessageDao();
		messageDao.queryMessageList(null, null);
	}
	
	/**
	 * 批量删除
	 */
	public void deleteBatch(List<Integer> ids) {
		DBAccess dbAccess=new DBAccess();
		
		SqlSession sqlSession=null;
		try {
			sqlSession=dbAccess.getSqlSession();
			//通过sqlSession执行sql语句
			sqlSession.delete("Message.deleteBatch",ids);
			sqlSession.commit();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			if(sqlSession!=null) {
				sqlSession.close();				
			}

		}
	}
	
	
	public void save(Message message) {
		DBAccess dbAccess=new DBAccess();
		
		SqlSession sqlSession=null;
		try {
			sqlSession=dbAccess.getSqlSession();
			//通过sqlSession执行sql语句
			sqlSession.insert("Message.save",message);
			sqlSession.commit();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			if(sqlSession!=null) {
				sqlSession.close();				
			}

		}
	}
	
	public Message queryById(String id) {
		DBAccess dbAccess=new DBAccess();
		List<Message> messageList = null;
		Message message=new Message();
		message.setId(id);
		SqlSession sqlSession=null;
		try {
			sqlSession=dbAccess.getSqlSession();
			//通过sqlSession执行sql语句
			messageList=sqlSession.selectList("Message.queryMessageList",message);
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			if(sqlSession!=null) {
				sqlSession.close();				
			}

		}
		
		return messageList.get(0);
	}
	
	public void update(Message message) {
		DBAccess dbAccess=new DBAccess();
		
		SqlSession sqlSession=null;
		try {
			sqlSession=dbAccess.getSqlSession();
			//通过sqlSession执行sql语句
			sqlSession.update("Message.update",message);
			sqlSession.commit();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			if(sqlSession!=null) {
				sqlSession.close();				
			}

		}
	}
/* public List<Message> queryMessageList(String command,String description) {
		List<Message> messageList = new ArrayList<Message>();
		SqlSession s=null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection conn=DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/micro_message", "root", "123456");
			StringBuffer sql=new StringBuffer("select ID,COMMAND,DESCRIPTION,CONTENT from message where 1=1 ");
			List<String> paramList=new ArrayList<>();
			if(command!=null&&!"".equals(command.trim())) {
				sql.append("and command = ? ");
				paramList.add(command);
			}
			if(description!=null&&!"".equals(description.trim())) {
				sql.append("and description like '%' ? '%' ");
				paramList.add(description);
			}
			PreparedStatement statement=conn.prepareStatement(sql.toString());
			for(int i=0;i<paramList.size();i++) {
				statement.setString(i+1, paramList.get(i));
			}
			ResultSet rs=statement.executeQuery();
			while(rs.next()) {
				Message message=new Message();
				messageList.add(message);
				
				message.setId(rs.getString("ID"));
				message.setCommand(rs.getString("COMMAND"));
				message.setDescription(rs.getString("DESCRIPTION"));
				message.setContent(rs.getString("CONTENT"));
			}
		} catch (ClassNotFoundException | SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return messageList;
	}*/
	
}
